{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setting up environment variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.environ['POSTGRE_USERNAME'] = 'postgres'\n",
    "os.environ['POSTGRE_PASSWORD'] = 'admin'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load value from environment variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "USER = os.environ['POSTGRE_USERNAME']\n",
    "PASSWORD = os.environ['POSTGRE_PASSWORD']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('postgres', 'admin')"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "USER, PASSWORD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting to Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(database='db_Advanced_SQL_Application_Development',\n",
    "                                       user=USER,\n",
    "                                       password=PASSWORD,\n",
    "                                       host='localhost',\n",
    "                                       port=5432)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<connection object at 0x0000003E7E1DD040; dsn: 'user=postgres password=xxx dbname=db_Advanced_SQL_Application_Development host=localhost port=5432', closed: 0>"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parameterizing SELECT statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all products type\n",
    "def getProductsByType(db_conn, prod_type):\n",
    "    cursor = db_conn.cursor()\n",
    "    sql_string = 'SELECT * FROM products WHERE product_type = %s'\n",
    "    cursor.execute(sql_string, [prod_type])\n",
    "    return (cursor.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get oven type products\n",
    "all_ovens = getProductsByType(conn, 'oven')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_ovens)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Reflector oven', 'oven'),\n",
       " (2, 'Convection microwave', 'oven'),\n",
       " (15, 'Masonry oven', 'oven'),\n",
       " (24, 'Microwave oven', 'oven'),\n",
       " (32, 'Russian oven', 'oven')]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_ovens[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Avoiding N+1 queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's say we want to get all product orders which include any type of ovens"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetchall_count:  1\n",
      "[(4, 1, 1, Decimal('296.47')), (23, 1, 4, Decimal('30.99')), (57, 1, 4, Decimal('124.53')), (63, 1, 2, Decimal('131.79'))] \n",
      "\n",
      "fetchall_count:  2\n",
      "[(5, 2, 2, Decimal('270.24')), (21, 2, 2, Decimal('196.39'))] \n",
      "\n",
      "fetchall_count:  3\n",
      "[(9, 15, 3, Decimal('53.26')), (38, 15, 2, Decimal('299.62')), (41, 15, 2, Decimal('158.61'))] \n",
      "\n",
      "fetchall_count:  4\n",
      "[(3, 24, 5, Decimal('131.20')), (32, 24, 5, Decimal('60.54')), (36, 24, 5, Decimal('93.13')), (49, 24, 4, Decimal('228.74')), (81, 24, 2, Decimal('283.90'))] \n",
      "\n",
      "fetchall_count:  5\n",
      "[(24, 32, 3, Decimal('70.57'))] \n",
      "\n",
      "fetchall_count:  6\n",
      "[(38, 33, 4, Decimal('236.11')), (40, 33, 1, Decimal('227.86')), (61, 33, 2, Decimal('45.44')), (82, 33, 3, Decimal('29.96'))] \n",
      "\n",
      "fetchall_count:  7\n",
      "[(12, 34, 3, Decimal('251.16')), (28, 34, 4, Decimal('72.18')), (69, 34, 5, Decimal('73.06')), (71, 34, 2, Decimal('75.19'))] \n",
      "\n",
      "fetchall_count:  8\n",
      "[(35, 37, 4, Decimal('211.71')), (49, 37, 1, Decimal('162.63')), (51, 37, 5, Decimal('271.15')), (61, 37, 2, Decimal('256.34'))] \n",
      "\n",
      "fetchall_count:  9\n",
      "[(56, 38, 4, Decimal('262.44')), (67, 38, 5, Decimal('237.32')), (71, 38, 5, Decimal('286.17')), (75, 38, 4, Decimal('96.06'))] \n",
      "\n",
      "fetchall_count:  10\n",
      "[] \n",
      "\n",
      "fetchall_count:  11\n",
      "[(8, 42, 2, Decimal('99.52')), (18, 42, 1, Decimal('281.35'))] \n",
      "\n",
      "fetchall_count:  12\n",
      "[(46, 43, 5, Decimal('207.13'))] \n",
      "\n",
      "fetchall_count:  13\n",
      "[(16, 47, 1, Decimal('246.68')), (45, 47, 1, Decimal('26.16')), (48, 47, 3, Decimal('262.16'))] \n",
      "\n",
      "fetchall_count:  14\n",
      "[(12, 48, 4, Decimal('20.08')), (21, 48, 5, Decimal('52.86')), (41, 48, 3, Decimal('148.91')), (43, 48, 4, Decimal('120.45')), (59, 48, 2, Decimal('164.11')), (85, 48, 5, Decimal('232.08'))] \n",
      "\n",
      "fetchall_count:  15\n",
      "[(2, 58, 5, Decimal('102.97')), (25, 58, 4, Decimal('85.03')), (28, 58, 2, Decimal('289.46')), (38, 58, 3, Decimal('255.12')), (40, 58, 2, Decimal('247.79')), (80, 58, 1, Decimal('95.93'))] \n",
      "\n",
      "fetchall_count:  16\n",
      "[(46, 67, 3, Decimal('257.33'))] \n",
      "\n",
      "fetchall_count:  17\n",
      "[(14, 72, 2, Decimal('118.64')), (77, 72, 3, Decimal('103.48'))] \n",
      "\n",
      "fetchall_count:  18\n",
      "[(27, 74, 1, Decimal('202.45')), (50, 74, 5, Decimal('290.62')), (62, 74, 4, Decimal('158.41'))] \n",
      "\n",
      "fetchall_count:  19\n",
      "[(23, 78, 3, Decimal('245.55')), (24, 78, 4, Decimal('21.83'))] \n",
      "\n",
      "fetchall_count:  20\n",
      "[(2, 80, 1, Decimal('181.54')), (26, 80, 3, Decimal('30.75')), (58, 80, 3, Decimal('185.41')), (68, 80, 5, Decimal('175.18')), (82, 80, 2, Decimal('140.09'))] \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# global cursor\n",
    "cursor = conn.cursor()\n",
    "fetchall_count = 0\n",
    "\n",
    "for oven in all_ovens:\n",
    "    oven_id = oven[0]\n",
    "    sql_string = 'SELECT * FROM product_orders WHERE product_id = %s'\n",
    "    \n",
    "    cursor.execute(sql_string, [oven_id])\n",
    "    oven_orders = cursor.fetchall()\n",
    "    fetchall_count += 1\n",
    "    \n",
    "    print('fetchall_count: ', fetchall_count)\n",
    "    print(oven_orders, \"\\n\")\n",
    "\n",
    "    \n",
    "# destory global cursor\n",
    "del cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE:**\n",
    "\n",
    "In above query, we are like check against one item and get all orders of specific item. Drop them back at home.\n",
    "\n",
    "Then again go back to store and check against another item and get all orders of that item. Drop them back at home again.\n",
    "\n",
    "This make inefficient and we want to get all orders of all related items while querying and bring them back to home all together."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## more efficient way"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getProductOrdersByType(db_conn, product_type):\n",
    "    cursor = db_conn.cursor()\n",
    "    \n",
    "    sql_string = 'SELECT po.* \\\n",
    "                        FROM product_orders po \\\n",
    "                        JOIN products p \\\n",
    "                        ON p.product_id = po.product_id  \\\n",
    "                        WHERE p.product_type = %s'\n",
    "    \n",
    "    cursor.execute(sql_string, [product_type])\n",
    "    return (cursor.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_product_orders = getProductOrdersByType(conn, 'oven')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "62"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_product_orders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 58, 5, Decimal('102.97')),\n",
       " (2, 80, 1, Decimal('181.54')),\n",
       " (3, 24, 5, Decimal('131.20')),\n",
       " (4, 1, 1, Decimal('296.47')),\n",
       " (5, 2, 2, Decimal('270.24'))]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_product_orders[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Challenge\n",
    "- write a function that executes an SQL query to reterive all product orders.\n",
    "- type: fryer\n",
    "- quanity greater than 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getAllProductOrdersByType(db_conn, product_type, quantity_greater_than):\n",
    "    \n",
    "    cursor = db_conn.cursor()\n",
    "    sql_string = 'SELECT po.* \\\n",
    "                        FROM product_orders po \\\n",
    "                        JOIN products p \\\n",
    "                        ON p.product_id = po.product_id \\\n",
    "                        WHERE p.product_type = %s \\\n",
    "                        AND po.quantity > %s'\n",
    "    \n",
    "#     sql_string = 'SELECT po.* \\\n",
    "#                         FROM product_orders po \\\n",
    "#                         JOIN products p \\\n",
    "#                         ON p.product_id = po.product_id  \\\n",
    "#                         WHERE p.product_type = %s \\\n",
    "#                         AND po.quantity > 3'\n",
    "    \n",
    "    cursor.execute(sql_string, [product_type, quantity_greater_than])\n",
    "    return (cursor.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_product_orders = getAllProductOrdersByType(conn, 'fryer', 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_product_orders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 3, 5, Decimal('72.69')),\n",
       " (11, 3, 4, Decimal('296.83')),\n",
       " (18, 26, 5, Decimal('253.36')),\n",
       " (53, 3, 5, Decimal('161.05')),\n",
       " (53, 17, 4, Decimal('125.62')),\n",
       " (59, 54, 5, Decimal('272.47')),\n",
       " (70, 3, 4, Decimal('261.91'))]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_product_orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
